BEGIN TRANSACTION
GO
UPDATE VoucherDetails SET LOTNo='.' WHERE Len(LotNo) <=1 AND VoucherID IN (SELECT VoucherID FROM Voucher WHERE VoucherNo Like 'AI-%') AND ItemID IS NOT NULL
GO
COMMIT

-----------------------------------------------------------

BEGIN TRANSACTION
GO
ALTER TABLE dbo.VoucherDetails ADD
	AVNo varchar(15) NULL,
	AVDate datetime NULL,
	StoreLot varchar(15) NULL,
	TotalDays numeric(9, 2) NOT NULL CONSTRAINT DF_VoucherDetails_TotalDays DEFAULT ((0)),
	AdjustDays numeric(9, 2) NOT NULL CONSTRAINT DF_VoucherDetails_AdjustDays DEFAULT ((0)),
	NetDays numeric(9, 2) NOT NULL CONSTRAINT DF_VoucherDetails_NetDays DEFAULT ((0))
GO
COMMIT

-----------------------------------------------------------

USE [BSC]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 12/31/2019 16:41:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.Voucher.Remarks AS MasterRemarks, ISNULL(dbo.Voucher.CustomerName, '') 
                      AS CustomerName, dbo.Voucher.GroupID AS SubPartyID, ISNULL(dbo.Groups.GroupName, '.') AS SubPartyName, dbo.Voucher.BankAccountID, 
                      dbo.Accounts.AccountTitle AS BankAccountTitle, dbo.Accounts.NameUrdu AS BankNameUrdu, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.Voucher.Amount AS SumAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.Voucher.TotalAmount, dbo.Voucher.NetAmount, dbo.Voucher.Cash, 
                      dbo.Voucher.StoreID, dbo.Voucher.VehicleNo, dbo.Voucher.StoreLot, Accounts_1.NameUrdu, dbo.VoucherDetails.AccountNo, Accounts_1.AccountTitle, 
                      Accounts_1.ParentAccount, dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.DrOrCr, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, 
                      dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Qty, 
                      dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, dbo.VoucherDetails.Rate, dbo.VoucherDetails.RoomID, dbo.Room.RoomCode, dbo.Room.RoomName, 
                      dbo.Room.RoomNameUrdu, dbo.VoucherDetails.RackID, dbo.Rack.RackCode, dbo.Rack.RackName, dbo.Rack.RackNameUrdu, dbo.VoucherDetails.ManzalID, 
                      dbo.Manzal.ManzalCode, dbo.Manzal.ManzalName, dbo.Manzal.ManzalNameUrdu, dbo.VoucherDetails.LotNo, dbo.VoucherDetails.Marka, 
                      dbo.VoucherDetails.AVDate, dbo.VoucherDetails.TotalDays, dbo.VoucherDetails.AdjustDays, dbo.VoucherDetails.NetDays
FROM         dbo.Groups RIGHT OUTER JOIN
                      dbo.Accounts RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Accounts.AccountNo = dbo.Voucher.BankAccountID ON dbo.Groups.GroupID = dbo.Voucher.GroupID RIGHT OUTER JOIN
                      dbo.Manzal RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID ON dbo.Manzal.ManzalID = dbo.VoucherDetails.ManzalID LEFT OUTER JOIN
                      dbo.Room ON dbo.VoucherDetails.RoomID = dbo.Room.RoomID LEFT OUTER JOIN
                      dbo.Rack ON dbo.VoucherDetails.RackID = dbo.Rack.RackID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.VoucherDetails.AccountNo = Accounts_1.AccountNo ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     VoucherDetails_1.VoucherID, Voucher_1.VoucherNo, Voucher_1.VoucherDate, Voucher_1.Remarks AS MasterRemarks, ISNULL(Voucher_1.CustomerName, '') 
                      AS CustomerName, Voucher_1.GroupID AS SubPartyID, ISNULL(Groups_1.GroupName, '.') AS SubPartyName, Voucher_1.BankAccountID, 
                      Accounts_2.AccountTitle AS BankAccountTitle, Accounts_2.NameUrdu AS BankNameUrdu, Voucher_1.SessionID, SessionInfo_1.SessionTitle, 
                      Voucher_1.Amount AS SumAmount, Voucher_1.Expense, Voucher_1.Discount, Voucher_1.TotalAmount, Voucher_1.NetAmount, Voucher_1.Cash, Voucher_1.StoreID, 
                      Voucher_1.VehicleNo, Voucher_1.StoreLot, Accounts_1.NameUrdu, VoucherDetails_1.AccountNo, Accounts_1.AccountTitle, Accounts_1.ParentAccount, 
                      VoucherDetails_1.Remarks, VoucherDetails_1.Amount, 0 AS Debit, VoucherDetails_1.Amount AS Credit, VoucherDetails_1.DrOrCr, VoucherDetails_1.VoucherDetailID, 
                      vw_Items_1.GroupID, vw_Items_1.GroupCode, vw_Items_1.GroupName, vw_Items_1.GroupNameUrdu, VoucherDetails_1.ItemID, vw_Items_1.ItemCode, 
                      vw_Items_1.ItemName, vw_Items_1.ItemNameUrdu, vw_Items_1.InUnit, VoucherDetails_1.Qty, 0 AS InQty, VoucherDetails_1.Qty AS OutQty, VoucherDetails_1.Rate, 
                      VoucherDetails_1.RoomID, Room_1.RoomCode, Room_1.RoomName, Room_1.RoomNameUrdu, VoucherDetails_1.RackID, Rack_1.RackCode, Rack_1.RackName, 
                      Rack_1.RackNameUrdu, VoucherDetails_1.ManzalID, Manzal_1.ManzalCode, Manzal_1.ManzalName, Manzal_1.ManzalNameUrdu, VoucherDetails_1.LotNo, 
                      VoucherDetails_1.Marka, VoucherDetails_1.AVDate, VoucherDetails_1.TotalDays, VoucherDetails_1.AdjustDays, VoucherDetails_1.NetDays
FROM         dbo.Groups AS Groups_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Voucher AS Voucher_1 ON Accounts_2.AccountNo = Voucher_1.BankAccountID ON Groups_1.GroupID = Voucher_1.GroupID RIGHT OUTER JOIN
                      dbo.Manzal AS Manzal_1 RIGHT OUTER JOIN
                      dbo.VoucherDetails AS VoucherDetails_1 LEFT OUTER JOIN
                      dbo.vw_Items AS vw_Items_1 ON VoucherDetails_1.ItemID = vw_Items_1.ItemID ON Manzal_1.ManzalID = VoucherDetails_1.ManzalID LEFT OUTER JOIN
                      dbo.Room AS Room_1 ON VoucherDetails_1.RoomID = Room_1.RoomID LEFT OUTER JOIN
                      dbo.Rack AS Rack_1 ON VoucherDetails_1.RackID = Rack_1.RackID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON VoucherDetails_1.AccountNo = Accounts_1.AccountNo ON Voucher_1.VoucherID = VoucherDetails_1.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON Voucher_1.SessionID = SessionInfo_1.SessionID
WHERE     (VoucherDetails_1.DrOrCr = N'Cr')